﻿-- skeleton used to construct a query that retrieves an ordered list of assemblies

WITH 
AssemblyDepth
AS
(
SELECT assembly_id, referenced_assembly_id, 1 as depth FROM %dbname%.sys.assembly_references AS R1 
WHERE NOT EXISTS (SELECT * FROM %dbname%.sys.assembly_references AS R2
WHERE R2.referenced_assembly_id = R1.assembly_id)
UNION ALL
SELECT R2.referenced_assembly_id as assembly_id,
(SELECT R3.referenced_assembly_id FROM %dbname%.sys.assembly_references R3 where
R3.assembly_id = R2.referenced_assembly_id) as referenced_assembly_id,
 depth + 1 as depth 
FROM %dbname%.sys.assemblies R1
JOIN AssemblyDepth AS R2
ON R1.assembly_id = R2.assembly_id
),
Assemblies 
AS
(
SELECT A.assembly_id, ISNULL((SELECT depth FROM AssemblyDepth WHERE assembly_id = A.assembly_id), 0) as depth,
(SELECT referenced_assembly_id FROM AssemblyDepth where assembly_id = A.assembly_id) as referenced_assembly_id 
FROM %dbname%.sys.assemblies AS A
WHERE name IN (
%assemblies%
)
UNION ALL
SELECT A1.referenced_assembly_id as assembly_id, 
(SELECT depth FROM AssemblyDepth WHERE assembly_id = A1.referenced_assembly_id) as depth,
NULL
FROM %dbname%.sys.assembly_references AS A1
JOIN
Assemblies AS A2
ON A2.assembly_id = A1.assembly_id
)
SELECT A2.name, MAX(A.depth) AS depth FROM Assemblies AS A
JOIN %dbname%.sys.assemblies AS A2 
ON A2.assembly_id = A.assembly_id
GROUP BY A2.Name
ORDER BY depth DESC

